/*
 * Copyright (C) 2009 Jakub Miara
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */
package com.araim.rringtone.db;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.media.RingtoneManager;
import android.util.Log;

import com.araim.rringtone.RandomRingtone;
import com.araim.rringtone.RingtoneInfo;

public class RandomRingtoneDatabase extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "araim.RandomRingtone";
	private static final int DATABASE_VERSION = 5;

	public RandomRingtoneDatabase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db
				.execSQL("CREATE TABLE ringtones (_id INTEGER PRIMARY KEY autoincrement, id INTEGER , name TEXT not null ,uri TEXT not null, rtype INTEGER not null DEFAULT 1)");
		db.execSQL("CREATE TABLE specific_mappings (specificId INTEGER , rid INTEGER)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion < 4) {
			db
					.execSQL("CREATE TABLE ringtones2 (_id INTEGER PRIMARY KEY autoincrement,id INTEGER , name TEXT not null ,uri TEXT not null)");
			db.execSQL("INSERT INTO ringtones2 (id,name,uri) SELECT id,name,uri FROM ringtones");
			db.execSQL("DROP TABLE ringtones");
			db.execSQL("ALTER TABLE ringtones2 RENAME TO ringtones");
			db.execSQL("ALTER TABLE ringtones ADD COLUMN rtype INTEGER not null DEFAULT 1");
		}
		db.execSQL("CREATE TABLE specific_mappings (specificId INTEGER , rid INTEGER)");
	}

	public void insertRingtone(RingtoneInfo ri) {
		SQLiteDatabase db = this.getWritableDatabase();
		long rowid = db.insert("ringtones", "", ri.getContentValues());
		Cursor c = db.query("ringtones", new String[] { "_id" }, "rowid = ?", new String[] { String.valueOf(rowid) }, null, null, null);
		if (c.moveToNext()) {
			ri.set_Id(c.getInt(0));
		}
		c.close();
		db.close();
	}

	public void insertAlarmRingtone(RingtoneInfo ri, int alarmid) {
		SQLiteDatabase db = this.getWritableDatabase();
		long rowid = db.insert("ringtones", "", ri.getContentValues());
		Cursor c = db.query("ringtones", new String[] { "_id" }, "rowid = ?", new String[] { String.valueOf(rowid) }, null, null, null);
		if (c.moveToNext()) {
			ri.set_Id(c.getInt(0));
		}
		c.close();
		ContentValues cv = new ContentValues();
		cv.put("specificId", alarmid);
		cv.put("rid", ri.get_Id());
		db.insert("specific_mappings", null, cv);
		db.close();
	}

	public void delete(RingtoneInfo ri) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete("ringtones", "_id = ? AND uri = ? AND rtype = ?", new String[] { String.valueOf(ri.getId()), ri.getUri(),
				String.valueOf(ri.getRtype()) });
		db.close();
	}

	public void insertRingtones(RingtoneInfo[] ris) {
		SQLiteDatabase db = this.getWritableDatabase();
		for (RingtoneInfo ri : ris) {
			db.insert("ringtones", "", ri.getContentValues());
		}
	}

	public void clear(int type) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("DELETE FROM ringtones WHERE rtype = ?", new Object[] { type });
		db.close();
	}

	public void clearAlarms(int alarmid) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("DELETE FROM specific_mappings WHERE specificId = ?", new Object[] { alarmid });
		db.execSQL("DELETE FROM ringtones WHERE rtype = " + RingtoneManager.TYPE_ALARM
				+ " AND NOT EXISTS (SELECT rid FROM specific_mappings  WHERE rid = _id)", new Object[0]);
		db.close();
	}

	public RingtoneInfo[] getAllRingtonesArray(int type) {
		return getAllRingtones(type).toArray(new RingtoneInfo[0]);
	}

	public ArrayList<RingtoneInfo> getAllRingtones(int type, int id) {
		ArrayList<RingtoneInfo> ringtones = new ArrayList<RingtoneInfo>();
		SQLiteDatabase db = null;
		Cursor c = null;
		try {
			db = this.getReadableDatabase();
			c = db.rawQuery("SELECT _id,id,name,uri,rtype FROM ringtones "
					+ "LEFT JOIN specific_mappings ON specific_mappings.rid = ringtones._id "
					+ "WHERE ringtones.rtype = ? AND specific_mappings.specificid = ?", new String[] { String.valueOf(type),
					String.valueOf(id) });
			if (c.moveToFirst()) {
				while (!c.isAfterLast()) {
					RingtoneInfo r = new RingtoneInfo();
					r.set_Id(c.getInt(0));
					r.setId(c.getInt(1));
					r.setName(c.getString(2));
					r.setUri(c.getString(3));
					r.setRtype(c.getInt(4));
					ringtones.add(r);
					c.moveToNext();
				}
			}
		} catch (Exception e) {
			Log.e(RandomRingtone.TAG, "Exception fetching ringtones: " + e.toString());
		} finally {
			if (c != null) {
				c.close();
			}
			if (db != null) {
				db.close();
			}
		}
		return ringtones;
	}

	public ArrayList<RingtoneInfo> getAllRingtones(int type) {
		// this is to workaround a bug causing the first get of the DB to do an
		// upgrade - if the first isn't getWritable, then upgrade is preformed
		// in read-only mode and fails.
		ArrayList<RingtoneInfo> ringtones = new ArrayList<RingtoneInfo>();
		SQLiteDatabase db = this.getWritableDatabase();
		db.close();
		db = null;
		Cursor c = null;
		try {
			db = this.getReadableDatabase();
			c = db.query("ringtones", new String[] { "_id", "id", "name", "uri", "rtype" }, "rtype = ?", new String[] { String
					.valueOf(type) }, null, null, null);
			if (c.moveToFirst()) {
				while (!c.isAfterLast()) {
					RingtoneInfo r = new RingtoneInfo();
					r.set_Id(c.getInt(0));
					r.setId(c.getInt(1));
					r.setName(c.getString(2));
					r.setUri(c.getString(3));
					r.setRtype(c.getInt(4));
					ringtones.add(r);
					c.moveToNext();
				}
			}
		} catch (Exception e) {
			Log.e(RandomRingtone.TAG, "Exception fetching ringtones: " + e.toString());
		} finally {
			if (c != null) {
				c.close();
			}
			if (db != null) {
				db.close();
			}
		}
		return ringtones;
	}

}
